package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import model.FIRModel;
import util.dataUtil;

public class FIRDAO {
	public static ArrayList getAll(){
		ArrayList listfir = new ArrayList();
		String sql = "select cd.id,firstname,lastname,title_name,name,cd.STATUS_CRIME,cr.last_updated from crime_detail cd,crime_type ct,Criminal_reg cr where cr.id=cd.idcriminal and cd.IDCRIM_TYPE=ct.id";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				FIRModel model = new FIRModel();
				model.setId(rs.getInt("id"));
				model.setFirstname(rs.getString("firstname"));
				model.setName(rs.getString("lastname"));
				model.setTitle_Name(rs.getString("title_Name"));
				model.setCrim_type(rs.getString("name"));
				model.setStatus_crime(rs.getInt("status_Crime"));
				model.setLast_Update(rs.getString("last_Updated"));
				listfir.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listfir;
	}
	//search fir
	public static ArrayList getsearchFIR(String title_name){
		ArrayList listfir = new ArrayList();
		String sql = "select cd.id,firstname,lastname,title_name,name,cd.STATUS_CRIME,cr.last_updated from crime_detail cd,crime_type ct,Criminal_reg cr where cr.id=cd.idcriminal and cd.IDCRIM_TYPE=ct.id and  cd.title_name like '%"+title_name+"%'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				FIRModel model = new FIRModel();
				model.setId(rs.getInt("id"));
				model.setFirstname(rs.getString("firstname"));
				model.setName(rs.getString("lastname"));
				model.setTitle_Name(rs.getString("title_Name"));
				model.setCrim_type(rs.getString("name"));
				model.setStatus_crime(rs.getInt("status_Crime"));
				model.setLast_Update(rs.getString("last_Updated"));
				listfir.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listfir;
	}
	
	//getbyid
	public static ArrayList<FIRModel> getbyid(int id){
		FIRModel model = null;
		ArrayList<FIRModel> list = new ArrayList<FIRModel>();
		String sql = "select * from CRIME_DETAIL where id=?";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ps.setString(1, "id");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				model = new FIRModel();
				model.setId(rs.getInt("id"));
				model.setIdcriminal(rs.getInt("idcriminal"));
				model.setIdcrim_Type(rs.getInt("idcrim_Type"));
				model.setTitle_Name(rs.getString("title_Name"));
				model.setTime(rs.getInt("time"));
				model.setStatus_crime(rs.getInt("status_Crime"));
				model.setDate_Temp(rs.getString("date_Temp"));
				model.setDate_Start(rs.getString("date_Start"));
				model.setDate_End(rs.getString("date_End"));
				model.setLast_Update(rs.getString("last_Updated"));
				list.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static FIRModel getbyid(String id){
		FIRModel model = null;
		String sql = "select title_name,most_wanted,status_crime,date_temp,date_start,date_end,idcriminal,idhis " +
				"		from crime_detail,crame" +
				"		where crime_detail.idcrim_type = crame.crimeid and id = ? and idhis = ?";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ps.setString(1, "id");
			ps.setString(2, "idhis");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				model = new FIRModel();
				model.setId(rs.getInt("id"));
				model.setIdcriminal(rs.getInt("idcriminal"));
				model.setIdcrim_Type(rs.getInt("idcrim_Type"));
				model.setTitle_Name(rs.getString("title_Name"));
				model.setStatus_crime(rs.getInt("status_Crime"));
				model.setDate_Temp(rs.getString("date_Temp"));
				model.setDate_Start(rs.getString("date_Start"));
				model.setDate_End(rs.getString("date_End"));
				model.setLast_Update(rs.getString("last_Updated"));
				model.setIdhis(rs.getInt("idhist"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return model;
	}
	public static String getnames(String table,int id){
		String name = null;
		String sql = "select*from "+table+" "+"where id ='"+id+"'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				name = rs.getString("name");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return name;
		
	}
	public static String getnames_criminal_reg(int id){
		String name = null;
		String sql = "select*from criminal_reg where id ='"+id+"'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				name = rs.getString("firstname");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return name;
		
	}
	
	public static int getid(String table,String name){
		int a =0;
		String sql = "select * from " +" "+table+" "+ " where name ='"+name+"'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				a = rs.getInt("id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
		
	}

	public static int insertFIR(FIRModel model){
		int result = 0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_InsertCRIME_DETAIL(?,?,?,?,?,?,?,?,?,?)}");
			csmt.setInt("idcriminal", model.getIdcrim_Type());
			csmt.setInt("idcrim_type", model.getIdcrim_Type());
			csmt.setString("title_Name", model.getTitle_Name());
			csmt.setInt("status_Crime", model.getStatus_crime());
			csmt.setInt("time", model.getTime());
			csmt.setString("date_Temp", model.getDate_Temp());
			csmt.setString("date_Start", model.getDate_Start());
			csmt.setString("date_End", model.getDate_End());
			csmt.setString("last_Updated", model.getLast_Update());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			result = csmt.getInt("result");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
		public static int updatefir(FIRModel model){
			int result = 0;
			try {
				CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_Update(?,?,?,?,?,?,?,?,?,?,?}");
				csmt.setInt("id", model.getId());
				csmt.setInt("idcriminal", model.getIdcriminal());
				csmt.setInt("idcrim_Type", model.getIdcrim_Type());
				csmt.setString("title_Name", model.getTitle_Name());
				csmt.setInt("status_Crime", model.getStatus_crime());
				csmt.setInt("time", model.getTime());
				csmt.setString("date_Temp", model.getDate_Temp());
				csmt.setString("date_Start", model.getDate_Start());
				csmt.setString("date_End", model.getDate_End());
				csmt.setString("last_Update", model.getLast_Update());
				csmt.registerOutParameter("result", java.sql.Types.INTEGER);
				csmt.execute();
				result = csmt.getInt("result");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return result;
		}
		public static int deletefir(FIRModel model){
			int result = 0;
			try {
				CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_DeleteCRIME_DETAIL(?,?)}");
				csmt.setInt("id", model.getId());
				csmt.registerOutParameter("result", java.sql.Types.INTEGER);
				csmt.execute();
				result = csmt.getInt("result");
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return result;
		}
		public static String convertID(int id){
			String convert = null;
			String sql = "select*from crime_detail where id ='" +id+ "'";
			try {
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while(rs.next()){
					convert = Integer.toString(id);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return convert;
	}
		public static int getid(int id){
			int iden = 0;
			String sql = "select*from crime_detail where id ='" +id+ "'";
			try {
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while(rs.next()){
					iden = rs.getInt("id");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return iden;
	}
		//getname
		public static int getid_Crime(String name){
			int iden = 0;
			String sql = "select * from table_NameCrime where name ='" +name+ "'";
			try {
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while(rs.next()){
					iden = rs.getInt("id");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return iden;
	}
		
		//get index
		public static int getIndex_Crime(String name){
			int iden = 1;
			String sql = "select * from table_NameCrime";
			try {
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while(rs.next()){
					if(rs.getString("name").equals(name))
					break;
					iden++;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return iden;
	}
}

